In [1]:
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore")

import upsetplot
from upsetplot import generate_counts, plot, UpSet

from plotly_upset.plotting import plot_upset
from retentioneering.eventstream import Eventstream

Customers Intersection Analysis for Pet Brands¶

  1. Sequence of Registrations in Brands
  2. Sequence of Orders in Brands
  3. Customer Distribution by Number of Brands
  4. Brand Preferences Among Customers
  5. Brand Customer Intersection Scheme
  6. Channels Intersection Scheme
In [2]:
df_clients_1 = pd.read_csv('df_clients_done_01.csv')
df_clients_2 = pd.read_csv('df_clients_done_02.csv')
clients = pd.concat([df_clients_1, df_clients_2])
columns = pd.DataFrame(clients.columns)
In [3]:
registration_dates = clients[['CustomerIdsMindboxId',
                              'CustomerCustomFieldsAnicuraRegistrationDate',
                              'CustomerCustomFieldsKinpetRegistrationDate',
                              'CustomerCustomFieldsLubimchikRegistrationDate',
                              'CustomerCustomFieldsPetstoryRegistrationDate',
                              'CustomerCustomFieldsRoyalcaninRegistrationDate']]

registration_dates.columns=['id', 'Anicura','Kinpet','Lubimchik','Petstory','Royalcanin']

registration_dates = pd.melt(registration_dates, id_vars=['id'], var_name='brand', value_name='timestamp')
registration_dates = registration_dates[registration_dates['timestamp'].notna()]
In [4]:
regs_1 = pd.read_csv('df_reg_done_01.csv')
regs_2 = pd.read_csv('df_reg_done_02.csv')
regs_3 = pd.read_csv('df_reg_done_03.csv')

regs = pd.concat([regs_1,regs_2,regs_3])

actions = regs[['CustomerActionCustomerIdsMindboxId','CustomerActionCreationDateTimeUtc','CustomerActionBrandIdsSystemName','CustomerActionActionTemplateName']]
actions.columns=['id','timestamp','brand','action']
In [5]:
first_action = actions.groupby('id').agg({'timestamp':'min'})
first_action = first_action.reset_index()

first_action = first_action.merge(actions, on=['id','timestamp'])
In [6]:
first_action['timestamp'] = pd.to_datetime(first_action['timestamp']).dt.date
In [7]:
df_orders = pd.read_csv('df_orders_other_brands.csv')
df_orders_royal = pd.read_csv('df_orders_royal_canin.csv')

df_orders_royal = df_orders_royal[['OrderCustomerIdsMindboxId','OrderFirstActionDateTimeUtc']]
df_orders_royal['brand'] = 'RoyalCanin'
df_orders_royal.columns=['id','timestamp','brand']

df_orders = df_orders[['OrderCustomerIdsMindboxId','OrderFirstActionDateTimeUtc','OrderFirstActionChannelIdsExternalId']]

df_orders.columns=['id','timestamp','brand']

df_orders = pd.concat([df_orders,df_orders_royal])

df_orders.loc[df_orders['brand'].str.contains('Lubimchik', na=False), 'brand'] = 'Lubimchik'
df_orders.loc[df_orders['brand'].str.contains('PetStory', na=False), 'brand'] = 'PetStory'
df_orders.loc[df_orders['brand'].str.contains('Kinpet', na=False), 'brand'] = 'Kinpet'
df_orders.loc[df_orders['brand'].str.contains('Anicura', na=False), 'brand'] = 'Anicura'
df_orders.loc[df_orders['brand'].str.contains('Белый клык', na=False), 'brand'] = 'Белый клык'
In [8]:
first_order = df_orders.groupby('id').agg({'timestamp':'min'})
first_order = first_order.reset_index()

first_order = first_order.merge(df_orders, on=['id','timestamp'])
In [9]:
registration_dates = pd.concat([first_action, first_order, registration_dates])
In [10]:
registration_dates['timestamp'] = pd.to_datetime(registration_dates['timestamp'])
In [11]:
registration_dates = registration_dates.replace({'Petstory':'PetStory'})
In [12]:
registration_dates = registration_dates[registration_dates['brand'].str.contains('Lubimchik|Anicura|PetNutrition|Kinpet|RoyalCanin|PetStory',na=False)]
In [13]:
registration_dates = registration_dates[['id','timestamp','brand']].drop_duplicates().sort_values(by=['id','timestamp']).drop_duplicates(subset=['id','brand'], keep='first')
In [14]:
registration_dates.columns=['user_id','timestamp','event']

Sequence of Registrations in Brands ¶

In [15]:
stream = Eventstream(registration_dates)
stream\
    .add_start_end_events()\
    .transition_graph(
    edges_norm_type=None,
    edges_weight_col='user_id'
    )
Out[15]:
<retentioneering.tooling.transition_graph.transition_graph.TransitionGraph at 0x1fd768ce670>

Sequence of Orders in Brands¶

In [16]:
df_orders.columns=['user_id','timestamp','event']

stream = Eventstream(df_orders)

stream\
    .add_start_end_events()\
    .transition_graph(
    edges_norm_type=None,
    edges_weight_col='user_id'
    )
Out[16]:
<retentioneering.tooling.transition_graph.transition_graph.TransitionGraph at 0x1fa32ffc5b0>
In [17]:
contacts = clients[['CustomerIdsMindboxId','CustomerEmail','CustomerMobilePhone']]
contacts.columns=['id','email','phone']
In [18]:
anicura_clients = clients[['CustomerIdsMindboxId',
                           'CustomerIdsAnicuraID',
                           'CustomerCustomFieldsAnicuraEmail',
                           'CustomerCustomFieldsAnicuraMobilephone',
                           'CustomerCustomFieldsAnicuraRegistrationDate',
                           'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]

anicura_clients = pd.DataFrame(anicura_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
anicura_clients = anicura_clients[anicura_clients[0]>0]
In [19]:
anicura_clients = clients[['CustomerIdsMindboxId',
                           'CustomerIdsAnicuraID',
                           'CustomerCustomFieldsAnicuraEmail',
                           'CustomerCustomFieldsAnicuraMobilephone',
                           'CustomerCustomFieldsAnicuraRegistrationDate',
                           'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]

anicura_clients = pd.DataFrame(anicura_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
anicura_clients = anicura_clients[anicura_clients[0]>0]
anicura_clients[0] = 'Anicura'
anicura_clients = anicura_clients.reset_index()
anicura_clients.columns=['id','Anicura']
In [20]:
kinpet_clients = clients[['CustomerIdsMindboxId',
                          'CustomerCustomFieldsKinpetActivity',
                          'CustomerCustomFieldsKinpetBreedPet',
                          'CustomerCustomFieldsKinpetEmail',
                          'CustomerCustomFieldsKinpetIDrbc',
                          'CustomerCustomFieldsKinpetmobilephone',
                          'CustomerCustomFieldsKinpetMobilePhoneConfirmed',
                          'CustomerCustomFieldsKinpetNameCompany',
                          'CustomerCustomFieldsKinpetPermission',
                          'CustomerCustomFieldsKinpetPro',
                          'CustomerCustomFieldsKinpetRegisterConfirmed',
                          'CustomerCustomFieldsKinpetRegisteredFromLanding',
                          'CustomerCustomFieldsKinpetRegistrationDate',
                          'CustomerCustomFieldsKinpetSellerRole',
                          'CustomerCustomFieldsKinpetTelegram',
                          'CustomerCustomFieldsKinpetTypeClient',
                          'CustomerCustomFieldsKinpetTypeGroupCustomer',
                          'CustomerIdsKinpetContactID',
                          'CustomerIdsKinpetID',
                          'CustomerCustomerSubscriptionsKinpetIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetSmsIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetWebPushIsSubscribed']]

kinpet_clients = pd.DataFrame(kinpet_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
kinpet_clients = kinpet_clients[kinpet_clients[0]>0]
kinpet_clients[0] = 'Kinpet'
kinpet_clients = kinpet_clients.reset_index()
kinpet_clients.columns=['id','Kinpet']
In [21]:
lubimchik_clients = clients[['CustomerIdsMindboxId',
                             'CustomerCustomFieldsCITYLubimchik',
                             'CustomerCustomFieldsLubimchikAccountActivity',
                             'CustomerCustomFieldsLubimchikEmail',
                             'CustomerCustomFieldsLubimchikMobilephone',
                             'CustomerCustomFieldsLubimchikPermission',
                             'CustomerCustomFieldsLubimchikPL',
                             'CustomerCustomFieldsLubimchikRegistrationDate',
                             'CustomerCustomFieldsLubimchikTelegram',
                             'CustomerCustomFieldsRecentlyLubimchik',
                             'CustomerIdsLubimchikID',
                             'CustomerIdsLubimchikSailplay',
                             'CustomerCustomerSubscriptionsLubimchikIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikSmsIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikViberIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikMobilePushIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikWebPushIsSubscribed']]

lubimchik_clients = pd.DataFrame(lubimchik_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
lubimchik_clients = lubimchik_clients[lubimchik_clients[0]>0]
lubimchik_clients[0] = 'Lubimchik'
lubimchik_clients = lubimchik_clients.reset_index()
lubimchik_clients.columns=['id','Lubimchik']
In [22]:
petnutrition_clients = clients[['CustomerIdsMindboxId',
                                'CustomerCustomFieldsPetNutritionActivityStatus',
                                'CustomerCustomFieldsPetNutritionBrand',
                                'CustomerCustomFieldsPetNutritionCampaign1Status',
                                'CustomerCustomFieldsPetNutritionCampaign2Status',
                                'CustomerCustomFieldsPetNutritionCampaign3Status',
                                'CustomerCustomFieldsPetNutritionEmail',
                                'CustomerCustomFieldsPetNutritionEmailConfirmed',
                                'CustomerCustomFieldsPetNutritionFavoriteFood',
                                'CustomerCustomFieldsPetNutritionLastEcomSite',
                                'CustomerCustomFieldsPetNutritionLastEmailStatus',
                                'CustomerCustomFieldsPetNutritionMobilephone',
                                'CustomerCustomFieldsPetNutritionPermission',
                                'CustomerCustomFieldsPetNutritionPermissionPD',
                                'CustomerCustomFieldsPetNutritionShelter',
                                'CustomerIdsPetNutritionUserID',
                                'CustomerCustomerSubscriptionsPetNutritionIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionSmsIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionViberIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionMobilePushIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionWebPushIsSubscribed']]

petnutrition_clients = pd.DataFrame(petnutrition_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
petnutrition_clients = petnutrition_clients[petnutrition_clients[0]>0]
petnutrition_clients[0] = 'PetNutrition'
petnutrition_clients = petnutrition_clients.reset_index()
petnutrition_clients.columns=['id','PetNutrition']
In [23]:
petstory_clients = clients[['CustomerIdsMindboxId',
                            'CustomerCustomFieldsPetStoryApproval',
                            'CustomerCustomFieldsPetStoryEmail',
                            'CustomerCustomFieldsPetStoryMobilephone',
                            'CustomerCustomFieldsPetStoryPermission',
                            'CustomerCustomFieldsPetStoryPushNotifications',
                            'CustomerIdsPetStoryOwnerID',
                            'CustomerCustomerSubscriptionsPetStoryIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryWebPushIsSubscribed',
                            'CustomerCustomFieldsPetstoryRegistrationDate',
                            'CustomerCustomFieldsPetstoryRegistrationSource',]]

petstory_clients = pd.DataFrame(petstory_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
petstory_clients = petstory_clients[petstory_clients[0]>0]
petstory_clients[0] = 'PetStory'
petstory_clients = petstory_clients.reset_index()
petstory_clients.columns=['id','PetStory']
In [24]:
royalcanin_clients = clients[['CustomerIdsMindboxId',
                              'CustomerCustomFieldsRoyalCaninBreederClub',
                              'CustomerCustomFieldsRoyalCaninBreedsCats',
                              'CustomerCustomFieldsRoyalCaninBreedsDogs',
                              'CustomerCustomFieldsRoyalCaninContactType',
                              'CustomerCustomFieldsRoyalCaninemail',
                              'CustomerCustomFieldsRoyalCaninEmailConfirmed',
                              'CustomerCustomFieldsRoyalCaninFiasArea',
                              'CustomerCustomFieldsRoyalCaninFiasCity',
                              'CustomerCustomFieldsRoyalCaninFiasRegion',
                              'CustomerCustomFieldsRoyalCaninIS18',
                              'CustomerCustomFieldsRoyalCaninMARSconsentdate',
                              'CustomerCustomFieldsRoyalCaninmobilephone',
                              'CustomerCustomFieldsRoyalCaninMobilePhoneConfirmed',
                              'CustomerCustomFieldsRoyalCaninPermission',
                              'CustomerCustomFieldsRoyalCaninPetOwner',
                              'CustomerCustomFieldsRoyalCaninSource',
                              'CustomerCustomFieldsRoyalCaninWorkPlace',
                              'CustomerIdsRoyalCaninCRMID',
                              'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
                              'CustomerCustomFieldsRoyalcaninRegistrationDate']]

royalcanin_clients = pd.DataFrame(royalcanin_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
royalcanin_clients = royalcanin_clients[royalcanin_clients[0]>0]
royalcanin_clients[0] = 'RoyalCanin'
royalcanin_clients = royalcanin_clients.reset_index()
royalcanin_clients.columns=['id','RoyalCanin']
In [25]:
royalcanin_clients = clients[['CustomerIdsMindboxId',
                              'CustomerCustomFieldsRoyalCaninBreederClub',
                              'CustomerCustomFieldsRoyalCaninBreedsCats',
                              'CustomerCustomFieldsRoyalCaninBreedsDogs',
                              'CustomerCustomFieldsRoyalCaninContactType',
                              'CustomerCustomFieldsRoyalCaninemail',
                              'CustomerCustomFieldsRoyalCaninEmailConfirmed',
                              'CustomerCustomFieldsRoyalCaninFiasArea',
                              'CustomerCustomFieldsRoyalCaninFiasCity',
                              'CustomerCustomFieldsRoyalCaninFiasRegion',
                              'CustomerCustomFieldsRoyalCaninIS18',
                              'CustomerCustomFieldsRoyalCaninMARSconsentdate',
                              'CustomerCustomFieldsRoyalCaninmobilephone',
                              'CustomerCustomFieldsRoyalCaninMobilePhoneConfirmed',
                              'CustomerCustomFieldsRoyalCaninPermission',
                              'CustomerCustomFieldsRoyalCaninPetOwner',
                              'CustomerCustomFieldsRoyalCaninSource',
                              'CustomerCustomFieldsRoyalCaninWorkPlace',
                              'CustomerIdsRoyalCaninCRMID',
                              'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
                              'CustomerCustomFieldsRoyalcaninRegistrationDate']]

royalcanin_clients = pd.DataFrame(royalcanin_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
royalcanin_clients = royalcanin_clients[royalcanin_clients[0]>0]
royalcanin_clients[0] = 'RoyalCanin'
royalcanin_clients = royalcanin_clients.reset_index()
royalcanin_clients.columns=['id','RoyalCanin']
In [26]:
brands_clients = anicura_clients.merge(
    kinpet_clients, on='id', how='outer').merge(
    lubimchik_clients, on='id', how='outer').merge(
    petnutrition_clients, on='id', how='outer').merge(
    petstory_clients, on='id', how='outer').merge(
    royalcanin_clients, on='id', how='outer')
In [27]:
brands_amount_by_clients = pd.DataFrame(brands_clients.groupby('id').count().sum(axis=1)).reset_index()
brands_amount_by_clients.columns = ['id','brands_amount']
In [28]:
brands_clients = brands_clients.merge(brands_amount_by_clients, on=['id'])
In [29]:
brands_amount_contribution = brands_amount_by_clients.groupby('brands_amount').agg({'id':'nunique'})
brands_amount_contribution['%'] = brands_amount_contribution['id']/brands_amount_contribution['id'].sum()*100
brands_amount_contribution = brands_amount_contribution.reset_index()
In [30]:
active = df_orders[df_orders['timestamp'] >= '2023-05-01'].groupby('event').agg({'user_id':'nunique'})
In [31]:
active = active.reset_index()
active.columns=['brand','6-Month Active Customers']

In [33]:
import plotly.graph_objects as go

labels = ['1','2','>3']
values = [2870200, 115797, 7756]

fig = go.Figure(data=[go.Pie(labels=labels, values=values,
                             marker=dict(colors=['#666','#e2001a','#FFC069']),
                             textfont_size=15,
                             pull=[0, 0, 0.2],
                             hoverinfo='label+value'
                            ) 
                     ])
fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=1.02,
    xanchor="right",
    x=1
))

fig.update_layout(title='Customer Distribution by Number of Brands',
                  font=dict(size=12, color="black"))
fig.show("notebook")
In [34]:
brands_clients = brands_clients.merge(contacts, on='id', how='left')
In [35]:
clients_by_brands = pd.DataFrame(brands_clients.count()).reset_index()[1:7]
clients_by_brands.columns=['brand','customers']
clients_by_brands['доля от базы'] = clients_by_brands['customers'] / len(brands_clients) * 100
#clients_by_brands['доля от базы'] = round(clients_by_brands['доля от базы']).astype(str) + '%'
In [36]:
clients_by_brands_1 = pd.DataFrame(brands_clients[brands_clients['brands_amount']==1].count()).reset_index()[1:7]
clients_by_brands_1.columns=['brand','only 1']
In [37]:
clients_by_brands_with_contacts = pd.DataFrame(brands_clients[brands_clients['email'].notna()|brands_clients['phone'].notna()].count()).reset_index()[1:7]
clients_by_brands_with_contacts.columns=['brand','with contacts']
In [38]:
clients_by_brands = clients_by_brands.merge(clients_by_brands_1,on='brand').merge(clients_by_brands_with_contacts, on='brand').merge(active, on='brand')
In [39]:
clients_by_brands = clients_by_brands.sort_values(by='customers',ascending=False)

In [40]:
import plotly.graph_objects as go
import pandas as pd

df = clients_by_brands

fig = go.Figure(data=[go.Table(
    header=dict(values=['Brand','Customers','% of Total','Single-Brand Customers', "With Contacts", 'Last 6-Month Customers'],
                font_color='white',
                fill_color='#666',
                align='left'),
    cells=dict(values=[df.brand, df.customers, round(df['доля от базы'],1).astype(str)+'%', df['only 1'], df['with contacts'], df['6-Month Active Customers']],
               fill_color='white',
               align='left'))
])
fig.update_layout(title='Brand Preferences Among Customers',
                  font=dict(size=12, color="black"))
fig.show("notebook")
In [41]:
brands_clients = brands_clients.fillna('')

In [42]:
venn_viz = brands_clients.copy()

venn_viz = venn_viz.replace({'':0,
                             'Anicura':1,
                             'Kinpet':1,
                             'Lubimchik':1,
                             'PetNutrition':1,
                             'PetStory':1,
                             'RoyalCanin':1
                            })
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]

fig = plot_upset(
    dataframes=[venn_viz],
    legendgroups=[''],
    marker_size=10,
    sorted_x="d",
    horizontal_spacing = 0.075,
    vertical_spacing = 0.1,
    column_widths=[0.2, 0.8],
    exclude_zeros=True,
    marker_colors=['#666']
)

fig.update_layout(
    width=1300,
    height=700,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Brand Customer Intersection Scheme',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [43]:
venn_viz = brands_clients[brands_clients['brands_amount']==2].copy()

venn_viz = venn_viz.replace({'':0,
                             'Anicura':1,
                             'Kinpet':1,
                             'Lubimchik':1,
                             'PetNutrition':1,
                             'PetStory':1,
                             'RoyalCanin':1
                            })
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]

fig = plot_upset(
    dataframes=[venn_viz],
    legendgroups=[''],
    marker_size=10,
    sorted_x="d",
    horizontal_spacing = 0.075,
    vertical_spacing = 0.1,
    column_widths=[0.2, 0.8],
    exclude_zeros=True,
    marker_colors=['#666']
)

fig.update_layout(
    width=1000,
    height=700,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Brand Customer Intersection Scheme<br>[by 2]',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [44]:
venn_viz = brands_clients[brands_clients['brands_amount']==3].copy()

venn_viz = venn_viz.replace({'':0,
                             'Anicura':1,
                             'Kinpet':1,
                             'Lubimchik':1,
                             'PetNutrition':1,
                             'PetStory':1,
                             'RoyalCanin':1
                            })
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]

fig = plot_upset(
    dataframes=[venn_viz],
    legendgroups=[''],
    marker_size=10,
    sorted_x="d",
    horizontal_spacing = 0.075,
    vertical_spacing = 0.1,
    column_widths=[0.2, 0.8],
    exclude_zeros=True,
    marker_colors=['#666']
)

fig.update_layout(
    width=1000,
    height=700,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Brand Customer Intersection Scheme<br>[by 3]',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [45]:
clients_id = clients[['CustomerIdsMindboxId',
                      'CustomerIdsAnicuraID',
                      'CustomerIdsKinpetID',
                      'CustomerIdsCraveID',
                      'CustomerIdsLubimchikID',
                      'CustomerIdsPetNutritionUserID',
                      'CustomerIdsPetStoryOwnerID',
                      'CustomerIdsRoyalCaninCRMID']]
In [46]:
clients_id = clients_id.rename(columns = {'CustomerIdsMindboxId':'ID',
                                          'CustomerIdsAnicuraID':'AnicuraID',
                                          'CustomerIdsKinpetID':'KinpetID',
                                          'CustomerIdsCraveID':'CraveID',
                                          'CustomerIdsLubimchikID':'LubimchikID',
                                          'CustomerIdsPetNutritionUserID':'PetNutritionID',
                                          'CustomerIdsPetStoryOwnerID':'PetStoryID',
                                          'CustomerIdsRoyalCaninCRMID':'RoyalCaninID'})
In [47]:
active_users = df_orders[df_orders['timestamp'] >= '2023-05-01'][['user_id']].drop_duplicates()
active_users.columns=['id']
In [48]:
active_users['active'] = True
In [49]:
brands_clients = brands_clients.merge(active_users, on=['id'], how='left')
In [50]:
subscriptions = clients[[
                         'CustomerCustomerSubscriptionsMarsIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsViberIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionViberIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikViberIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]
In [51]:
subscriptions = subscriptions[[
    'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
    'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
    'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
    'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
    'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
    'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
    'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
    'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
    'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
    'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
    'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed'
]]
In [52]:
subscriptions.columns=[
    'PetStory Sms',
    'PetStory Viber',
    'PetStory MobilePush',
    'PetStory Email',
    'Kinpet Viber and Sms',
    'Kinpet Email',
    'Kinpet MobilePush',
    'Anicura All Channels',
    'RoyalCanin Email',
    'Lubimchik Email',
    'PetNutrition Email']
In [53]:
subscriptions = subscriptions.replace({True:1,False:0})
In [54]:
subscriptions = subscriptions.fillna(0).astype(int)
In [55]:
subscriptions = subscriptions.reset_index().drop('index', axis=1)
In [56]:
subscriptions['sum'] = subscriptions.sum(axis=1)
In [57]:
subscriptions = subscriptions[subscriptions['sum']>=2]
In [58]:
subscriptions = subscriptions.drop('sum', axis=1)

In [59]:
fig = plot_upset(
                dataframes=[subscriptions],
                legendgroups=[''],
                marker_size=10,
                sorted_x="d",
                horizontal_spacing = 0.075,
                vertical_spacing = 0.1,
                column_widths=[0.2, 0.8],
                exclude_zeros=True,
                marker_colors=['#666']
)

fig.update_layout(
    width=1900,
    height=900,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Channels Intersection Scheme',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")